DROP TRIGGER UPDATE_WORKER_ID_TRI;
DROP TRIGGER UPDATE_PROJECT_ID_TRI;
DROP SEQUENCE WORKER_ID_SEQ;
DROP SEQUENCE PROJECT_ID_SEQ;
DROP TABLE WORKERS;
DROP TABLE PROJECTS;

--创建表
CREATE TABLE PROJECTS(
  PROJECT_ID INT PRIMARY KEY,
  PROJECT_NAME VARCHAR2(20) NOT NULL,
  DURATION INT CHECK(DURATION>0) NOT NULL,
  INVESTMENT INT CHECK(INVESTMENT>0) NOT NULL,
  START_TIME DATE NOT NULL
);

CREATE TABLE WORKERS(
  WORKER_ID INT PRIMARY KEY,
  WORKER_NAME VARCHAR2(20) NOT NULL,
  SEX INTEGER NOT NULL,
  TELEPHONE VARCHAR2(11) NOT NULL,
  PROJECT_ID INT REFERENCES PROJECTS(PROJECT_ID)
);

--创建序列
CREATE SEQUENCE PROJECT_ID_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE;

CREATE SEQUENCE WORKER_ID_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE;

--创建触发器
CREATE TRIGGER UPDATE_PROJECT_ID_TRI BEFORE
INSERT ON PROJECTS FOR EACH ROW WHEN(NEW.PROJECT_ID IS NULL)
BEGIN
SELECT PROJECT_ID_SEQ.NEXTVAL INTO:NEW.PROJECT_ID FROM DUAL;
END;

CREATE TRIGGER UPDATE_WORKER_ID_TRI BEFORE
INSERT ON WORKERS FOR EACH ROW WHEN(NEW.WORKER_ID IS NULL)
BEGIN
SELECT WORKER_ID_SEQ.NEXTVAL INTO:NEW.WORKER_ID FROM DUAL;
END;
